Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# KEY

The KEY function returns the value of the specified base dimension for a value of a conjoint dimension or a composite.

Return Value

The return value depends on the data type of the specified base dimension.

Syntax

KEY(dimension-exp, base-dimension-exp)

Arguments

dimension-exp

  An expression that specifies a value of a conjoint dimension or a composite. When you specify the conjoint dimension itself, KEY uses the first value in status. When you specify the composite itself, KEY uses the first value in status for every base dimension in the composite.

base-dimension-exp

  An expression that specifies the name of a base dimension of the previously specified conjoint dimension or composite for which you want to know the dimension value.

Examples

Example 15-29 Reporting with a Conjoint

Suppose you want to produce a report of data dimensioned by a conjoint dimension. You can label each row with the base values of each conjoint dimension value with the KEY function. Each base value occupies its own column and you have more control over the layout.

The following program excerpt loops over the conjoint dimension `proddist`, whose values are a combination of `product` and `district`. Assume also that there is a variable named `dsales` which is dimensioned by `proddist`.

    DEFINE proddist DIMENSION <product district>
    LD Conjoint dimension made up of combinations of product and district values
    DEFINE dsales VARIABLE DECIMAL <month proddist>
    LD Sparse sales data made dense by dimensioning by conjoint dimension proddist

The program excerpt shows `dsales` for three months. The base values of the conjoint dimension value each occupy their own column. For contrast, the second loop uses the conjoint dimension directly, without the KEY function. The conjoint dimension values are displayed in one column, with angle brackets.

    LIMIT month TO FIRST 3
    FOR proddist
      ROW KEY(proddist district) KEY( proddist product) ACROSS month: dsales
    BLANK 2
    FOR proddist
      ROW W 25 proddist ACROSS month: dsales

The program produces the following report.

    Boston         Tents       32,153.52  32,536.30  43,062.75
    Denver         Canoes      45,467.80  51,737.01  58,437.11
    Atlanta        Sportswear 114,446.26 123,164.92 138,601.64
    <Tents, Boston>            32,153.52  32,536.30  43,062.75
    <Canoes, Denver>           45,467.80  51,737.01  58,437.11
    <Sportswear, Atlanta>     114,446.26 123,164.92 138,601.64